From 412fdc314cf7e4900d27ae1a9380977892567699 Mon Sep 17 00:00:00 2001
From: Stevan Earl <stevan.earl@asu.edu>
Date: Sun, 9 Aug 2026 18:20:59 -0700
Subject: [PATCH] feat: add A-record groom-scan conversion loader

Split from grm_scan commit fcdd838.
---
 conversion/load_groom_scan_arec.sql | 121 ++++++++++++++++++++++++++++
 1 file changed, 121 insertions(+)
 create mode 100644 conversion/load_groom_scan_arec.sql

diff --git a/conversion/load_groom_scan_arec.sql b/conversion/load_groom_scan_arec.sql
new file mode 100644
index 0000000..f93336f
--- /dev/null
+++ b/conversion/load_groom_scan_arec.sql
@@ -0,0 +1,121 @@
+-- Copyright (C) 2026 The Meme Factory, Inc.  http://www.karlpinc.com/
+--
+--   This program is free software: you can redistribute it and/or modify
+--   it under the terms of the GNU Affero General Public License as
+--   published by the Free Software Foundation, either version 3 of the
+--   License, or (at your option) any later version.
+--
+--   This program is distributed in the hope that it will be useful,
+--   but WITHOUT ANY WARRANTY; without even the implied warranty of
+--   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+--   GNU Affero General Public License for more details.
+--
+--   You should have received a copy of the GNU Affero General Public License
+--   along with this program.  If not, see <https://www.gnu.org/licenses/>.
+--
+-- Karl O. Pinc <kop@karlpinc.com>
+
+-- Setup the search path
+SET search_path TO sokwedb, codes, lib, clean, libconv;
+
+
+-- groom_scan_arec -> watches, events, roles
+
+DO $$
+DECLARE
+  this_gs clean.groom_scan_arec%ROWTYPE;
+  related_wid watches.wid%TYPE;
+  related_commid watches.commid%TYPE;
+  event_eid events.eid%TYPE;
+
+BEGIN
+  FOR this_gs IN
+    SELECT *
+      FROM clean.groom_scan_arec
+      ORDER BY groom_scan_arec.date
+             , groom_scan_arec.time
+             , groom_scan_arec.chimp_1
+             , groom_scan_arec.chimp_2
+  LOOP
+    -- Which watch belongs to the groom scan?
+    SELECT watches.wid
+      INTO related_wid
+      FROM watches
+      WHERE watches.animid = 'UNK'
+            AND watches.date = this_gs.date
+            AND watches.type = 'A';
+
+    IF NOT FOUND THEN
+      -- The community recorded for attendance on the scan date identifies
+      -- the community associated with the feeding-station groom scan.
+      SELECT DISTINCT attendance.a_cl_community_id
+        INTO STRICT related_commid
+        FROM clean.attendance
+        WHERE attendance.a_date = this_gs.date;
+
+      INSERT INTO watches (
+          animid
+        , commid
+        , date
+        , type
+        , notes)
+        VALUES (
+          'UNK'
+        , related_commid
+        , this_gs.date
+        , 'A'
+        , '')
+        RETURNING wid INTO related_wid;
+    END IF;
+
+    -- groom_scan_arec -> events
+    INSERT INTO events (
+        wid
+      , behavior
+      , start
+      , stop
+      , certainty
+      , notes)
+      VALUES (
+        related_wid
+      , 'AGSCAN'
+      , this_gs.time
+      , this_gs.time
+      , '1'
+      , '')
+      RETURNING eid INTO event_eid;
+
+    -- groom_scan_arec -> roles
+    CASE this_gs.direction
+      WHEN 'G' THEN
+        INSERT INTO roles (eid, role, participant)
+          VALUES (event_eid, 'Actor', this_gs.chimp_1);
+        INSERT INTO roles (eid, role, participant)
+          VALUES (event_eid, 'Actee', this_gs.chimp_2);
+      WHEN 'M' THEN
+        INSERT INTO roles (eid, role, participant)
+          VALUES (event_eid, 'Mutual', this_gs.chimp_1);
+        INSERT INTO roles (eid, role, participant)
+          VALUES (event_eid, 'Mutual', this_gs.chimp_2);
+      ELSE
+        RAISE data_exception USING
+          MESSAGE = 'Unknown GROOM_SCAN_AREC.Direction value',
+          DETAIL = 'Value (Direction) = ('
+                   || COALESCE(this_gs.direction, 'NULL')
+                   || '), Value (Date) = ('
+                   || this_gs.date
+                   || '), Value (TIME) = ('
+                   || COALESCE(this_gs.time::TEXT, 'NULL')
+                   || '), Value (Chimp_1) = ('
+                   || this_gs.chimp_1
+                   || '), Value (Chimp_2) = ('
+                   || this_gs.chimp_2
+                   || ')';
+    END CASE;
+  END LOOP;
+END;
+$$;
+
+ANALYZE watches;
+ANALYZE events;
+ANALYZE roles;
-- 
2.34.1